package com.example.booksystem.mapper;

import com.example.booksystem.model.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookMapper {
    public static final MySQL MYSQL = new MySQL();
    private static final String SELECT_BOOK = "select * from book where title like ? or press like ? or author like ?";//查询书籍名称
    private static final String SELECT_BOOK_NUM = "select num from book where id = ?";
    private static final String SELECT_ALL_BOOK = "select * from book";//获取全部图书
    private static final String BORROW_BOOK_DELETE = "update book set num = ? where id = ?";
    private static final String BORROW_BOOK_INSERT = "insert into borrow (bookid,userid) values (?,?)";
    private static final String DELETE_BOOK = "delete from book where id = ?";
    private static final String INSERT_BOOK = "insert into book (id,title,author,press,price,num) values (?,?,?,?,?,?)";
    private static final String SELECT_BORROW_BOOK = "select * from book where id in (select bookid from borrow where userid = ?)";
    private static final String DELETE_BORROW_BOOK = "delete from borrow where userid = ? and bookid = ?";
    public Integer deleteBorrowBook(Integer userId,String bookId) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(DELETE_BORROW_BOOK);
        statement.setInt(1,userId);
        statement.setString(2,bookId);
        Integer n = statement.executeUpdate();
        if (n == 1){
            statement = MYSQL.getStatement(SELECT_BOOK_NUM);
            statement.setString(1,bookId);
            ResultSet set = statement.executeQuery();
            Integer count = 0;
            Integer num = 0;
            while (set.next()){
                num = set.getInt("num");
                count++;
            }
            System.out.println(count);
            System.out.println(num);
            if (count == 1){
                statement = MYSQL.getStatement(BORROW_BOOK_DELETE);
                statement.setInt(1,num+1);
                statement.setString(2,bookId);
                n = MYSQL.excuteUpdate();
                System.out.println(n);
               return n;
            }
        }
        return -1;
    }
    public List<Book> selectBorrowBook(Integer userId) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(SELECT_BORROW_BOOK);
        statement.setInt(1,userId);
        return query(statement);
    }

    public List<Book> selectBook(String text) throws SQLException {
        StringBuffer str = new StringBuffer("%");
        for (int i = 0; i < text.length(); i++) {
            str.append(text.charAt(i) + "%");
        }
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(SELECT_BOOK);
        for (int i = 1; i <= 3; i++) {
            statement.setString(i,str.toString());
        }
        return query(statement);
    }
    public List<Book> selectAllBook() throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(SELECT_ALL_BOOK);
        return query(statement);
    }

    private List<Book> query(PreparedStatement statement) throws SQLException {
        List<Book> books = new ArrayList<>();
        ResultSet set = statement.executeQuery();
        while (set.next()){
            Book book = new Book();
            book.setId(set.getString("id"));
            book.setTitle(set.getString("title"));
            book.setAuthor(set.getString("author"));
            book.setPress(set.getString("press"));
            book.setPrice(String.valueOf(set.getDouble("price")));
            book.setNum(set.getInt("num"));
            System.out.println(book);
            books.add(book);
        }
        set.close();
        MYSQL.close();
        return books;
    }

    public Boolean borrowBook(Book book,Integer userId) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(SELECT_BOOK_NUM);
        statement.setString(1,book.getId());
        ResultSet set = statement.executeQuery();
        Integer count = 0;
        Integer num = 0;
        while (set.next()){
            num = set.getInt("num");
            count++;
        }
        System.out.println(count);
        System.out.println(num);
        if (count == 1){
            statement = MYSQL.getStatement(BORROW_BOOK_DELETE);
            statement.setInt(1,num-1);
            statement.setString(2,book.getId());
            Integer n = MYSQL.excuteUpdate();
            System.out.println(n);
            if (n == 1){
                System.out.println("删除成功");
                statement = MYSQL.getStatement(BORROW_BOOK_INSERT);
                statement.setString(1,book.getId());
                statement.setInt(2,userId);
                n = MYSQL.excuteUpdate();
                MYSQL.close();
                if (n == 1){
                    return true;
                }
            }
        }
        return false;
    }

    public Boolean updateBook(String key,String data,String id) throws SQLException {
        Connection connection = MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement("update book set " + key + " = ? where id = ?");
        statement.setString(1,data);
        statement.setString(2,id);
        Integer n = MYSQL.excuteUpdate();
        if (n == 1) {
            return true;
        }
        return false;
    }
    public Boolean updateBookPrice(String key,String data,String id) throws SQLException {
        Connection connection = MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement("update book set " + key + " = ? where id = ?");
        statement.setDouble(1,Double.valueOf(data));
        statement.setString(2,id);
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        if (n == 1) {
            return true;
        }
        return false;
    }

    public Boolean deleteBook(Book book) throws SQLException {
        Connection connection = MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(DELETE_BOOK);
        statement.setString(1,book.getId());
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        if (n == 1) {
            return true;
        }
        return false;
    }

    public Boolean insertBook(Book book) throws SQLException {
        Connection connection = MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(INSERT_BOOK);
        statement.setString(1,book.getId());
        statement.setString(2,book.getTitle());
        statement.setString(3,book.getAuthor());
        statement.setString(4,book.getPress());
        statement.setDouble(5,Double.valueOf(book.getPrice()));
        statement.setInt(6,book.getNum());
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        if (n == 1){
            return true;
        }
        return false;
    }
}
